#-*-coding:utf-8-*-
# 作者    ：SunDuWei
# 创作时间 ：2020-05-12

'''
future_data_L1.py作用：
1.期货合约基础信息爬取(tushare)
2.期货合约日线数据(tushare)
3.期货合约1min数据
4.期货合约现货数据(生意社)
5.期货合约板块日线数据（tushare）
默认起始日期：20170101
'''

import sqlite3
import tushare as ts
import pandas as pd
import requests
import re
from tqdm import tqdm
from time import sleep
import types
import numpy as np
import datetime
from DB_operate import *
from gm.api import *
set_token('de6943525068f5656c46458ef447751e5110b824')
ts.set_token('e6e5601134d8d0f15d2192e65e2551998dc2027c29f776834d5cd8f7')
pro = ts.pro_api()


# 期货合约基础信息
class future_info_data:
    '''
    基础信息类
    '''
    def __init__(self, end_date, start_date='20170101'):
        self.start_date = start_date
        self.end_date = end_date

    def info_data_reptile(self):
        exchange_list = ['CFFEX', 'DCE', 'CZCE', 'SHFE', 'INE']
        future_info = []
        for exchange in exchange_list:
            future_exchange = pro.fut_basic(exchange=exchange)
            future_info.append(future_exchange)
        future_info = pd.concat(future_info)
        future_info = future_info[['ts_code', 'symbol', 'exchange', 'fut_code', 'list_date', 'delist_date']]
        future_info = future_info.dropna()
        future_info['list_date'] = pd.to_numeric(future_info['list_date']).astype(int)
        future_info['delist_date'] = pd.to_numeric(future_info['delist_date']).astype(int)
        future_info = future_info[future_info['list_date'] < int(self.end_date)]
        future_info = future_info[future_info['delist_date'] > int(self.start_date)]
        future_info = future_info.rename(columns={'symbol': 'code'})
        future_info.reset_index(inplace=True, drop=True)
        return future_info


class future_day_data:

    '''
    日线数据类
    '''

    def __init__(self, end_date, start_date='20170101'):
        self.end_date = end_date
        self.start_date = start_date

    def day_data_reptile(self, ts_code, code):
        df = pro.fut_daily(ts_code=ts_code, start_date=self.start_date, end_date=self.end_date)
        df = df.rename(columns={'ts_code': 'code', 'trade_date': 'date', 'vol': 'volume'})
        need_columns = ['code', 'date', 'open', 'high', 'low', 'close', 'volume', 'amount', 'oi']
        df = df.loc[:, need_columns]
        df.code = code
        return df


class future_good_data:
    '''
    现货数据类
    '''

    def __init__(self, end_date, start_date):
        self.end_date = end_date
        self.start_date = start_date
        self.trade_date = transform.trade_date_series(self.end_date, self.start_date)

    def good_data_reptile(self):
        # 生意社网址
        df = pd.DataFrame()
        with tqdm(range(len(self.trade_date))) as t:
            for date, j in zip(self.trade_date, t):
                str_date = str(date)[0:4]+'-'+str(date)[4:6]+'-'+str(date)[-2:]
                url = r'http://www.100ppi.com/sf/day-%s.html' % str_date
                html = requests.get(url)
                text = html.text

                col = ['code_cn', 'good_price',
                       'next_month_code', 'next_month_price', 'next_month_basis_points', 'next_month_basis_percent',
                       'main_code', 'main_price', 'main_basis_points', 'main_basis_percent']

                model = '<tr  align="center"  bgcolor="#fafdff">.*?target=_blank>(.*?)</a>' \
                        + '.*?<td>(.*?)</td>' \
                        + '.*?<td>(.*?)</td>' \
                        + '.*?<td>(.*?)</td>' \
                        + '.*?<font color=.*?>(.*?)</font>' \
                        + '.*?<font color=.*?>(.*?)</font>' \
                        + '.*?<td>(.*?)</td>' \
                        + '.*?<td>(.*?)</td>' \
                        + '.*?<font color=.*?>(.*?)</font>' \
                        + '.*?<font color=.*?>(.*?)</font>'

                pattern = re.compile(model, re.S)
                markets = ['上海期货交易所', '郑州商品交易所', '大连商品交易所']
                mktlist = []
                # 找到各个交易所起始定位
                for mkt in markets:
                    mktlist.append(text.find(mkt))

                df_date = pd.DataFrame()
                for i in range(len(markets)):
                    # 判定交易所信息所在位置
                    if i == len(markets)-1:
                        end = len(text)
                    else:
                        end = mktlist[i+1]

                    start = mktlist[i]
                    data_solo = re.findall(pattern, text[start:end])
                    df_data = pd.DataFrame(data_solo, columns=col)

                    # 添加日期
                    df_data['date'] = date
                    df_date = df_date.append(df_data)

                    #删除“&nbsp;”
                    for k in ['good_price', 'next_month_code', 'next_month_price', 'main_code', 'main_price']:
                        df_date[k] = df_date[k].apply(lambda x: x.replace("&nbsp;", ''))

                df = df.append(df_date)
                # 避免被封ip
                sleep(0.6)
            df.reset_index(inplace=True, drop=True)
            t.close()
            return df


class future_index_data:
    '''
    期货南华指数数据（tushare）
    '''
    def __init__(self, end_date, start_date):
        self.end_date = end_date
        self.start_date = start_date

    def index_data_reptile(self):
        index_list = {'NHAI.NH':'南华农产品指数', 'NHCI.NH':'南华商品指数', 'NHECI.NH':'南华能化指数',
                      'NHFI.NH':'南华黑色指数','NHII.NH':'南华工业品指数',' NHMI.NH':'南华金属指数',
                      'NHNFI.NH':'南华有色金属','NHPMI.NH':'南华贵金属指数', 'A.NH':'南华连大豆指数',
                      'AG.NH':'南华沪银指数','AL.NH':'南华沪铝指数','AP.NH':'南华郑苹果指数','AU.NH':'南华沪黄金指数',
                      'BB.NH':'南华连胶合板指数','BU.NH':'南华沪石油沥青指数','C.NH':'南华连玉米指数','CF.NH':'南华郑棉花指数',
                      'CS.NH':'南华连玉米淀粉指数','CU.NH':'南华沪铜指数','CY.NH':'南华棉纱指数','ER.NH':'南华郑籼稻指数',
                      'FB.NH':'南华连纤维板指数','FG.NH':'南华郑玻璃指数','FU.NH':'南华沪燃油指数','HC.NH':'南华沪热轧卷板指数',
                      'I.NH':'南华连铁矿石指数','J.NH':'南华连焦炭指数','JD.NH':'南华连鸡蛋指数','JM.NH':'南华连焦煤指数',
                      'JR.NH':'南华郑粳稻指数','L.NH':'南华连乙烯指数','LR.NH':'南华郑晚籼稻指数','M.NH':'南华连豆粕指数',
                      'ME.NH':'南华郑甲醇指数','NI.NH':'南华沪镍指数','P.NH':'南华连棕油指数','PB.NH':'南华沪铅指数',
                      'PP.NH':'南华连聚丙烯指数','RB.NH':'南华沪螺钢指数','RM.NH':'南华郑菜籽粕指数','RO.NH':'南华郑菜油指数',
                      'RS.NH':'南华郑油菜籽指数','RU.NH':'南华沪天胶指数','SC.NH':'南华原油指数','SF.NH':'南华郑硅铁指数',
                      'SM.NH':'南华郑锰硅指数','SN.NH':'南华沪锡指数','SP.NH':'南华纸浆指数',
                      'SR.NH': '南华纸浆指数','TA.NH':'南华郑精对苯二甲酸指数','TC.NH':'南华郑动力煤指数','V.NH':'南华连聚氯乙烯指数',
                      'WR.NH':'南华沪线材指数','WS.NH':'南华郑强麦指数','Y.NH':'南华连豆油指数','ZN.NH':'南华沪锌指数'}

        index_data = pd.DataFrame()
        for index_code,cn_index in zip(index_list.keys(),index_list.values()):
            index_solo_data = pro.index_daily(ts_code=index_code, start_date=self.start_date, end_date=self.end_date)
            index_solo_data['cn_index'] = cn_index
            index_data = index_data.append(index_solo_data)
        index_data = index_data.rename(columns={'ts_code': 'code', 'trade_date': 'date', 'vol': 'volume'})
        index_data.reset_index(inplace=True, drop=True)
        # 转换amount的数据类型
        index_data['amount'] = pd.to_numeric(index_data['amount'], errors='coerce')
        return index_data


class future_min_data:
    '''
    主要用于爬取期货的分钟后过数据（掘金）
    '''
    def __init__(self, end_date, start_date):
        self.start_date, self.end_date = transform.day_date_to_min_date(start_date, end_date)

    def min_data_reptile(self, code, exchange):
        symbol = exchange + '.' + code
        min_data = history(symbol=symbol, frequency='60s', start_time=self.start_date, end_time=self.end_date,
                               df=True)
        # 区别大小写
        if len(min_data) == 0:
            symbol = exchange + '.' + code.lower()
            min_data = history(symbol=symbol, frequency='60s', start_time=self.start_date, end_time=self.end_date,
                               df=True)
        if len(min_data) == 0:
            return min_data
        else:
            # 避免数据过量
            append_data = []
            while True:
                if len(min_data) < 33000:
                    break
                else:
                    start_date_temp = min_data.loc[32999, 'eob']
                    if len(append_data) != 0:
                        start_date_temp = append_data[-1].loc[32999, 'eob']
                    temp_data = history(symbol=symbol, frequency='60s', start_time=start_date_temp, end_time=self.end_date,
                                       df=True)
                    append_data.append(temp_data)
                    if len(temp_data) < 33000:
                        break
            if len(append_data) != 0:
                df_append = pd.concat(append_data)
                min_data = min_data.append(df_append)

            # 修改格式
            min_data['datetime'] = min_data['eob'].apply(lambda x: x.strftime('%Y-%m-%d %H:%M:%S'))
            min_data['date'] = min_data['datetime'].apply(lambda x: int(x[0:4] + x[5:7] + x[8:10]))
            min_data['time'] = min_data['datetime'].apply(lambda x: int(x[11:13] + x[14:16]))
            min_data.rename(columns={'symbol': 'code', 'position': 'oi'}, inplace=True)
            columns_list = ['code', 'date', 'time', 'open', 'high', 'low', 'close', 'volume', 'amount', 'oi']
            min_data = min_data[columns_list]
            min_data.reset_index(drop=True, inplace=True)
            return min_data


class transform:
    @classmethod
    def trade_date_series(cls, end_date, start_date):
        trade_date = pro.trade_cal(exchange='SSE', start_date=start_date, end_date=end_date, is_open='1')
        trade_date.rename(columns={'cal_date': 'date'}, inplace=True)
        trade_date = trade_date['date']
        return trade_date

    @classmethod
    def columns_to_table_sql(cls, df, primary_key):
        '''
        :param df:传入的dataframe
        :param primary_key:主键列表list
        :return: 传出可以用于生成数据库表的sql语句
        '''
        dtype_sql_dict = {'primary_key': ''}
        for i in primary_key:
            dtype_sql_dict['primary_key'] = dtype_sql_dict['primary_key']+',' + i
        dtype_sql_dict['primary_key'] = dtype_sql_dict['primary_key'].replace(',', '', 1)
        for i in df.columns:
            if np.issubdtype(type(df.loc[0, i]), np.str):
                dtype_sql_dict[i] = 'TEXT'
            elif np.issubdtype(type(df.loc[0, i]), np.int):
                dtype_sql_dict[i] = 'INTEGER'
            elif np.issubdtype(type(df.loc[0, i]), np.float):
                dtype_sql_dict[i] = 'REAL'
            else:
                raise('不确定数据类型，建议修改')
            if i in primary_key:
                dtype_sql_dict[i] = dtype_sql_dict[i] + ' NOT NULL'
        return dtype_sql_dict

    @classmethod
    def table_primary_key_dict(cls):
        primary_key_dict = {'info_data': ['code', 'exchange'],
                            'good_data': ['code_cn', 'date'],
                            'index_data': ['code', 'date'],
                            'day_data': ['code', 'date']}
        return primary_key_dict

    @classmethod
    def day_date_to_min_date(cls, start_date, end_date):
        # 主要用于找到min数据期货的起始时间点和终止时间点
        str_start_date = str(start_date)[0:4] + '-' + str(start_date)[4:6] + '-' + str(start_date)[6:8]
        start_date = get_previous_trading_date('SZSE', str_start_date) + ' 21:00:00'
        str_end_date = str(end_date)[0:4] + '-' + str(end_date)[4:6] + '-' + str(end_date)[6:8]
        end_date = str_end_date + ' 15:00:00'
        return start_date, end_date


if __name__ == '__main__':
    #%% 基础信息
    start_date = '20170203'
    end_date = datetime.date.today().strftime('%Y%m%d')
    day_DB_name = r'./DB_file/future_day_data.db'
    min_DB_name = r'./DB_file/future_min_data.db'
    info_Table_name = 'info_data'
    good_Table_name = 'good_data'
    index_Table_name = 'index_data'
    day_Table_name = 'day_data'

    #%% 期货基础信息
    # 数据库操作对象
    conn = sqlite3.connect(day_DB_name)
    info_data_DB_work = Work_DB(day_DB_name, info_Table_name)
    start_date = str(info_data_DB_work.max_date_query(conn, 'list_date'))
    # 爬取数据
    info_data = future_info_data(end_date=end_date, start_date=start_date)
    df_info_data = info_data.info_data_reptile()
    # 入库
    primary_key_info = ['code', 'exchange']
    info_data_DB_work.insert_unduplicated_dataframe(df_info_data, conn, primary_key_info)
    conn.commit()

    #%% 期货日线数据
    # 数据库操作对象
    info_data = future_info_data(end_date=end_date, start_date='20170103')
    df_info_data = info_data.info_data_reptile()
    day_data_DB_work = Work_DB(day_DB_name, day_Table_name)
    df_day_data = []
    print('日线数据爬取中...')
    with tqdm(range(len(df_info_data))) as t:
        for ts_code, code, i, fut_code, delist_date in zip(
                df_info_data.ts_code, df_info_data.code, t, df_info_data.fut_code, df_info_data.delist_date):
            # 查询条件语句
            condition_sql = '%s = "%s"' % ('code', code)
            start_date = str(day_data_DB_work.max_date_query(conn, 'date', condition_sql=condition_sql))
            if int(start_date) >= int(delist_date) or int(start_date) >= int(end_date):
                continue
            day_data = future_day_data(end_date=end_date, start_date=start_date)
            df_data_solo = day_data.day_data_reptile(ts_code=ts_code, code=code)
            df_data_solo['fut_code'] = fut_code
            sleep(0.1)
            df_day_data.append(df_data_solo)
        t.close()
    df_day_data = pd.concat(df_day_data)
    df_day_data.reset_index(inplace=True, drop=True)
    # 入库
    primary_key_day = ['code', 'date']
    day_data_DB_work.insert_unduplicated_dataframe(df_day_data, conn, primary_key_day)
    conn.commit()

    #%% 期货的现货数据
    # 数据库操作对象
    good_data_DB_Work = Work_DB(day_DB_name, good_Table_name)
    start_date = str(good_data_DB_Work.max_date_query(conn, 'date'))
    # 爬取数据
    good_data = future_good_data(end_date=end_date, start_date=start_date)
    print('现货数据爬取中...')
    df_good_data = good_data.good_data_reptile()
    # 入库
    primary_key_good = ['code_cn', 'date']
    good_data_DB_Work.insert_unduplicated_dataframe(df_good_data, conn, primary_key_good)
    conn.commit()

    #%% 期货的指数数据
    # 数据库操作对象
    index_data_DB_Work = Work_DB(day_DB_name, index_Table_name)
    start_date = str(index_data_DB_Work.max_date_query(conn, 'date'))
    # 爬取数据
    index_data = future_index_data(end_date=end_date, start_date=start_date)
    df_index_data = index_data.index_data_reptile()
    # 入库
    primary_key_index = ['code', 'date']
    index_data_DB_Work.insert_unduplicated_dataframe(df_index_data, conn, primary_key_index)
    conn.commit()
    conn.close()

    #%% 分钟数据入库
    conn = sqlite3.connect(min_DB_name)
    primary_key_min = ['date', 'time']
    print('分钟数据入库中....')
    with tqdm(range(len(df_info_data))) as t:
        for code, exchange, start_date, delist_date, count in zip(
                df_info_data.code, df_info_data.exchange, df_info_data.list_date, df_info_data.delist_date, t):
            Table_name = code
            min_data_DB_Work = Work_DB(min_DB_name, Table_name)

            # 判断是否需要爬取
            cursor = conn.cursor()
            sql = r'SELECT count(*) FROM sqlite_master WHERE type="table" AND name="%s"' %(Table_name)
            cursor.execute(sql)
            value = cursor.fetchone()
            if value[0] == 1:
                start_date = str(min_data_DB_Work.max_date_query(conn, 'date'))
                if int(start_date) >= int(delist_date) or int(start_date) >= int(end_date):
                    continue

            # 爬取分钟数据
            min_data_work = future_min_data(start_date=start_date, end_date=end_date)
            df_min_data = min_data_work.min_data_reptile(code=code, exchange=exchange)
            if len(df_min_data) == 0:
                continue

            # 插入数据库
            if value[0] == 0:
                Coloumns_dict = transform.columns_to_table_sql(df_min_data, primary_key_min)
                min_data_DB_Work.table_establish(conn, Coloumns_dict)
            min_data_DB_Work.insert_unduplicated_dataframe(df_min_data, conn, primary_key_min)
            conn.commit()
        t.close()
    conn.close()











